import sys
from sqlalchemy import *

#####
###
### Connection Settings
###
### Used by SQLALchemy to set up interaction
engine = "mysql" 
uname = "uname"
passwd = "passwd"
hostAndPort = "localhost:3306"
database = "Census"

#####
###
### State & filename
###
### Use the full name of the state and the relative filename for the source of data.
### This data should be in a tab-separated plaintext file, with records delineated by
### newlines, with the first row containing header information.  
###
#####

state = "Washington"
stateAbbrev = "WA"

infile = "sample/patientFile.txt"
outfile = "sample/testOut.txt"
sumfile = "sample/testSum.txt"
stateoutfile = "sample/stateOutFile.txt"
statesumfile = "sample/stateSummaryFile.txt"

#####
###
### Other Info
###
#####
currentYear = 2009 

#####
###
### Labels 
###
### The following variables tell us what you call relevant fields in your data
###
#####
sexLabel = "GENDER"
birthYearLabel = "BIRTH_YEAR"
raceLabel = "RACE"

#####
###
### Thresholds 
###
### Defines which points of interest you're concerned with.  If individuals
### are in a group greater than threshold n, they're considered "safe,"
### and do not contribute to the re-idenfication risk.
###
#####
thresholds = [1,5, 10, 100, 20000]

#####
####
### THESAURUS OF ACCEPTABLE/SYNONOMOUS TERMS
### 
### In order to use the census as an source of data, we must have information
### to convert between the terminology used in the dataset, and the terminology 
### used in the census.
### 
### This section can also be used to create generalizations.
### 
### The thesauruses are python dictionaries with the key corresponding to the
### term used in the dataset, and the value a list of terms in the census which
### correspond.
####
#####
races = {"AA":["Black or African American alone"],
         "AI":["American Indian and Alaska Native alone"],
         "AS":["Asian alone"], 
         "AS ":["Asian alone"],
         "WH":["White alone"],
         "OR":["Some other race alone"],
         "UN":["Two or more races"],
         "PI":["Native Hawaiian and Other Pacific Islander alone"]}
  
other_races = ["American Indian and Alaska Native alone",
         "Asian alone",
         "Some other race alone",
         "Two or more races",
         "Native Hawaiian and Other Pacific Islander alone"]
races_generalized = {"AA":["Black or African American alone"],
         "AI": other_races, 
         "WH":["White alone"],
         "AS ": other_races,
         "AS": other_races,
         "OR": other_races,
         "UN": other_races,
         "PI": other_races}

genders = {"M":["Male"],
           "F":["Female"]}

######
###
### The main function of this file is just a test.  It confirms that the
### information provided regarding the database, etc. can be used to 
### establish a valid connection.  
### See an SQLAlchemy tutorial for information on setting up an engine.
###
#####
def main(argv):
    sqlengine = create_engine('%s://%s:%s@%s/%s'%\
                            (engine, uname, passwd, hostAndPort, database))
    sqlengine.echo = True
    metadata = MetaData(sqlengine)
    metadata.create_all()
    xn = sqlengine.connect()
    
    description = Table('fieldNames_County', metadata, autoload = True)
    records = Table('allStates_County', 
                    metadata,
                    Column('fieldname_id', 
                           String(10), 
                           ForeignKey(description.c.FieldName)),
                    autoload=True)
    ###
    # The first task is a dataset-specific query
    # Establish number of 24 year-old white females
    ###
    s = select([description.c.minAge, 
                description.c.maxAge,
                description.c.Race,
                description.c.Sex,
                func.sum(records.c.population).label("Pop")],
               from_obj=[records.join(description)])

    s = s.where(records.c.state == state)
    s = s.where(description.c.minAge <=24)
    s = s.where(description.c.maxAge >= 24)
    s = s.where(description.c.Race == "White alone")
    s = s.where(description.c.Sex == "Female")
    s = s.group_by(description.c.minAge, description.c.maxAge, description.c.Race, description.c.Sex)

    for row in xn.execute(s):
        print row["minAge"], row["Race"], row["Sex"], row["Pop"]

    ###
    # The second task is to establish groups
    # according to race, gender, and age in ten-year increments
    ###
    s = select([description.c.Race,
                description.c.Sex,
                text("fieldNames_County.minAge DIV 10 AS age10Yrs"),
                func.sum(records.c.population).label("Pop")],
               from_obj=[records.join(description)])
    s = s.where(records.c.state == state)
    s = s.group_by(description.c.Race, description.c.Sex, text("age10Yrs"))
    for row in xn.execute(s):
        print row["Race"], row["Sex"], row["age10Yrs"], row["Pop"]
    
if __name__ == '__main__':
    main(sys.argv[1:])
